﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Maticsoft.DBUtility;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    public class HRDept
    {
        /// <summary>
        /// 根据公司编号获取数据
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="entityNo">组织机构编号</param>
        /// <returns>部门信息集合</returns>
        public DataSet GetListByEntityNo(string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FEntityNo,FDeptNo,FDeptName,FIsAgency,FDeptProp,FSeq,FChargeLeader,FParentDeptNo,FIsEval ");
            strSql.Append(" FROM t_HRDept  where FEntityNo=@FEntityNo");
            strSql.Append(" ORDER BY FSeq");
            SqlParameter[] parameters = { 
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar, 15) };
            parameters[0].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 根据公司编号获取外派机构数据
        /// <creator>chenly</creator>
        /// <date>2013-09-27</date>
        /// </summary>
        /// <param name="entityNo">组织机构编号</param>
        /// <returns>部门信息集合</returns>
        public DataSet GetListEntityNo(string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FEntityNo,FDeptNo,FDeptName,FIsAgency,FDeptProp,FSeq,FChargeLeader,FParentDeptNo,FIsEval ");
            strSql.Append(" FROM t_HRDept  where FEntityNo=@FEntityNo and FIsAgency='Y'");
            SqlParameter[] parameters = { 
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar, 15) };
            parameters[0].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 根据公司编号获取需要计划总结的单位
        /// <creator>zhongyl</creator>
        /// <date>2013-09-27</date>
        /// </summary>
        /// <param name="entityNo">组织机构编号</param>
        /// <returns>部门信息集合</returns>
        public DataSet GetListByFIsReportPlanSummary(string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FEntityNo,FDeptNo,FDeptName,FIsAgency,FDeptProp,FSeq,FChargeLeader,FParentDeptNo,FIsEval ");
            strSql.Append(" FROM t_HRDept  where FEntityNo=@FEntityNo and FIsReportPlanSummary='Y' order by FSeq");
            SqlParameter[] parameters = { 
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar, 15) };
            parameters[0].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 根据公司编号获取数据(不包括派出机构)
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="entityNo">组织机构编号</param>
        /// <returns>部门信息集合</returns>
        public DataSet GetListBYFIsAgency(string entityNo, string strwhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FEntityNo,FDeptNo,FDeptName,FIsAgency,FDeptProp,FSeq,FChargeLeader,FParentDeptNo,FIsEval ");
            strSql.Append(" FROM t_HRDept  where FEntityNo=@FEntityNo and FIsAgency='N'");
            if (strwhere != "")
            {
                strSql.Append(" and " + strwhere);
            }
            SqlParameter[] parameters = { 
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar, 15) };
            parameters[0].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 根据公司编号获取数据(不包括派出机构和虚拟部门)
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="entityNo">组织机构编号</param>
        /// <returns>部门信息集合</returns>
        public DataSet GetListBYFIsAgencyProp(string entityNo, string strwhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FEntityNo,FDeptNo,FDeptName,FIsAgency,FDeptProp,FSeq,FChargeLeader,FParentDeptNo,FIsEval ");
            strSql.Append(" FROM t_HRDept  where FEntityNo=@FEntityNo and FIsAgency='N' and FDeptProp<>'9'");
            if (strwhere != "")
            {
                strSql.Append(" and " + strwhere);
            }
            SqlParameter[] parameters = { 
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar, 15) };
            parameters[0].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 根据公司编号获取数据 FDeptNo,FDeptName,FParentDeptNo
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="entityNo">组织机构编号</param>
        /// <returns>部门信息集合</returns>
        public DataSet GetListNoNameByEntityNo(string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FDeptNo,FDeptName,FParentDeptNo ");
            strSql.Append(" FROM t_HRDept  where FEntityNo=@FEntityNo");
            strSql.Append(" ORDER BY FSeq");
            SqlParameter[] parameters = { 
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar, 15) };
            parameters[0].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }



        /// <summary>
        /// 获取当前部门和所有下级部门的部门编号 返回类型为'1','2','3'
        /// <creator>zhongyl</creator>
        /// <date>2012-11-13</date>
        /// </summary>
        /// <param name="deptNo">部门编号</param>
        /// <param name="entityNo">组织机构编号</param>
        /// <returns>部门编号</returns>
        public DataSet GetSubDeptNo(string deptNo, string entityNo)
        {
            string strSql = "select FDeptNo from fn_GetSubHRDept(@FDeptNo,@FEntityNo)";
            SqlParameter[] parameters = { 
                    new SqlParameter("@FDeptNo", SqlDbType.NVarChar, 15), 
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar, 15)};

            parameters[0].Value = deptNo;
            parameters[1].Value = entityNo;

            return DbHelperSQL.Query(strSql, parameters);

        }

        /// <summary>
        /// 是否存在该记录
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fEntityNo">组织机构编码</param>
        /// <param name="fDeptNo">部门编号</param>
        /// <returns>是否存在</returns>
        public bool Exists(string FEntityNo, string FDeptNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_HRDept");
            strSql.Append(" where FEntityNo=@FEntityNo and FDeptNo=@FDeptNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FDeptNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = FEntityNo;
            parameters[1].Value = FDeptNo;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 删除部门和部门岗位数据 事务
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fEntityNo">组织机构编号</param>
        /// <param name="fDeptNo">部门编号</param>
        public void deleteDeptAndPosition(string FEntityNo, string FDeptNo)
        {
            CommandInfo cmds = new CommandInfo();
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            //删除部门岗位
            strSql.Append("delete from t_HRPosition ");
            strSql.Append(" where FDeptNo=@FDeptNo and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters0 = {
					new SqlParameter("@FDeptNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,15)};
            parameters0[0].Value = FDeptNo;
            parameters0[1].Value = FEntityNo;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters0;
            cmdList.Add(cmds);

            //删除部门   
            strSql = new StringBuilder();
            cmds = new CommandInfo();

            strSql.Append("delete from t_HRDept ");
            strSql.Append(" where FEntityNo=@FEntityNo and FDeptNo=@FDeptNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FDeptNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = FEntityNo;
            parameters[1].Value = FDeptNo;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);


            DbHelperSQL.ExecuteSqlTranWithString(cmdList);
        }

        /// <summary>
        /// 保存部门和部门岗位 事务模式
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>>
        /// </summary>
        /// <param name="model">部门信息</param>
        /// <param name="positionList">岗位信息</param>
        public void AddDeptAndPosition(Model.HRDept model, List<Model.HRPosition> positionList)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            //增加部门数据
            strSql.Append("insert into t_HRDept(");
            strSql.Append("FEntityNo,FDeptNo,FDeptName,FIsAgency,FDeptProp,FSeq,FChargeLeader,FParentDeptNo,FIsEval,FDeptShorName,FIsReportPlanSummary,FIsCheckSystem)");
            strSql.Append(" values (");
            strSql.Append("@FEntityNo,@FDeptNo,@FDeptName,@FIsAgency,@FDeptProp,@FSeq,@FChargeLeader,@FParentDeptNo,@FIsEval,@FDeptShorName,@FIsReportPlanSummary,@FIsCheckSystem)");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FDeptName", SqlDbType.NVarChar,50),
					new SqlParameter("@FIsAgency", SqlDbType.Char,1),
					new SqlParameter("@FDeptProp", SqlDbType.Char,1),
					new SqlParameter("@FSeq", SqlDbType.Int,4),
					new SqlParameter("@FChargeLeader", SqlDbType.NVarChar,15),
					new SqlParameter("@FParentDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FIsEval", SqlDbType.Char,1),
					new SqlParameter("@FDeptShorName", SqlDbType.NVarChar,20),
                    new SqlParameter("@FIsReportPlanSummary", SqlDbType.NVarChar,1),
                    new SqlParameter("@FIsCheckSystem", SqlDbType.NVarChar,1)
                                        };
            parameters[0].Value = model.FEntityNo;
            parameters[1].Value = model.FDeptNo;
            parameters[2].Value = model.FDeptName;
            parameters[3].Value = model.FIsAgency;
            parameters[4].Value = model.FDeptProp;
            parameters[5].Value = model.FSeq;
            parameters[6].Value = model.FChargeLeader;
            parameters[7].Value = model.FParentDeptNo;
            parameters[8].Value = model.FIsEval;
            parameters[9].Value = model.FDeptShorName;
            parameters[10].Value = model.FIsReportPlanSummary;
            parameters[11].Value = model.FIsCheckSystem;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            //增加部门岗位
            if (positionList.Count > 0)
            {
                strSql = new StringBuilder();
                strSql.Append("insert into t_HRPosition(");
                strSql.Append("FPositionNo,FDeptNo,FEntityNo,FPositionName,FSeq,FPositionProp)");
                strSql.Append(" values (");
                strSql.Append("@FPositionNo,@FDeptNo,@FEntityNo,@FPositionName,@FSeq,@FPositionProp)");
                string strSqls = strSql.ToString();

                foreach (Model.HRPosition positionObj in positionList)
                {
                    cmds = new CommandInfo();

                    SqlParameter[] parameters1 = {
					    new SqlParameter("@FPositionNo", SqlDbType.NVarChar,12),
					    new SqlParameter("@FDeptNo", SqlDbType.NVarChar,12),
					    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					    new SqlParameter("@FPositionName", SqlDbType.NVarChar,50),
					    new SqlParameter("@FSeq", SqlDbType.Int),
					    new SqlParameter("@FPositionProp", SqlDbType.Char,1)};

                    parameters1[0].Value = positionObj.FPositionNo;
                    parameters1[1].Value = model.FDeptNo;
                    parameters1[2].Value = model.FEntityNo;
                    parameters1[3].Value = positionObj.FPositionName;
                    parameters1[4].Value = positionObj.FSeq;
                    parameters1[5].Value = positionObj.FPositionProp;

                    cmds.CommandText = strSqls;
                    cmds.Parameters = parameters1;
                    cmdList.Add(cmds);
                }

            }

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);


        }


        /// <summary>
        ///更新部分字段
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="model">部门信息</param>
        public void UpdatePart(Model.HRDept model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_HRDept set ");
            strSql.Append("FDeptName=@FDeptName,");
            strSql.Append("FIsAgency=@FIsAgency,");
            strSql.Append("FDeptProp=@FDeptProp,");
            strSql.Append("FSeq=@FSeq,");
            strSql.Append("FChargeLeader=@FChargeLeader,");
            strSql.Append("FDeptShorName=@FDeptShorName,");
            strSql.Append("FIsReportPlanSummary=@FIsReportPlanSummary,");
            strSql.Append("FIsCheckSystem=@FIsCheckSystem");
            strSql.Append(" where FEntityNo=@FEntityNo and FDeptNo=@FDeptNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FDeptName", SqlDbType.NVarChar,50),
					new SqlParameter("@FIsAgency", SqlDbType.Char,1),
					new SqlParameter("@FDeptProp", SqlDbType.Char,1),
					new SqlParameter("@FSeq", SqlDbType.Int),
					new SqlParameter("@FChargeLeader", SqlDbType.NVarChar,15),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FDeptShorName", SqlDbType.NVarChar,20),
                    new SqlParameter("@FIsReportPlanSummary", SqlDbType.NVarChar,1),                     
                    new SqlParameter("@FIsCheckSystem", SqlDbType.NVarChar,1)                
                                        };
            parameters[0].Value = model.FDeptName;
            parameters[1].Value = model.FIsAgency;
            parameters[2].Value = model.FDeptProp;
            parameters[3].Value = model.FSeq;
            parameters[4].Value = model.FChargeLeader;
            parameters[5].Value = model.FEntityNo;
            parameters[6].Value = model.FDeptNo;
            parameters[7].Value = model.FDeptShorName;
            parameters[8].Value = model.FIsReportPlanSummary;
            parameters[9].Value = model.FIsCheckSystem;
            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取部门名称
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fEntityNo">组织机构编号</param>
        /// <param name="fDeptNo">部门编号</param>
        /// <returns>部门名称</returns>
        public string GetDeptName(string FEntityNo, string FDeptNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FDeptName from dbo.v_HRDept");
            strSql.Append(" where FEntityNo=@FEntityNo and FDeptNo=@FDeptNo");
            SqlParameter[] parameters ={
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12),
                    new SqlParameter("@FDeptNo",SqlDbType.NVarChar,12)};
            parameters[0].Value = FEntityNo;
            parameters[1].Value = FDeptNo;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (null != obj)
            {
                return obj.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        ///<creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fEntityNo">组织机构编号</param>
        /// <param name="fDeptNo">部门编号</param>
        /// <returns>部门信息</returns>
        public Model.HRDept GetModel(string FEntityNo, string FDeptNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FEntityNo,FDeptNo,FDeptName,FIsAgency,FDeptProp,FSeq,FChargeLeader,FParentDeptNo,FIsEval,FDeptShorName,FIsReportPlanSummary,FIsCheckSystem from t_HRDept ");
            strSql.Append(" where FEntityNo=@FEntityNo and FDeptNo=@FDeptNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FDeptNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = FEntityNo;
            parameters[1].Value = FDeptNo;

            Model.HRDept model = new Model.HRDept();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["FEntityNo"] != null && ds.Tables[0].Rows[0]["FEntityNo"].ToString() != "")
                {
                    model.FEntityNo = ds.Tables[0].Rows[0]["FEntityNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FDeptNo"] != null && ds.Tables[0].Rows[0]["FDeptNo"].ToString() != "")
                {
                    model.FDeptNo = ds.Tables[0].Rows[0]["FDeptNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FDeptName"] != null && ds.Tables[0].Rows[0]["FDeptName"].ToString() != "")
                {
                    model.FDeptName = ds.Tables[0].Rows[0]["FDeptName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FIsAgency"] != null && ds.Tables[0].Rows[0]["FIsAgency"].ToString() != "")
                {
                    model.FIsAgency = ds.Tables[0].Rows[0]["FIsAgency"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FDeptProp"] != null && ds.Tables[0].Rows[0]["FDeptProp"].ToString() != "")
                {
                    model.FDeptProp = ds.Tables[0].Rows[0]["FDeptProp"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FSeq"] != null && ds.Tables[0].Rows[0]["FSeq"].ToString() != "")
                {
                    model.FSeq = int.Parse(ds.Tables[0].Rows[0]["FSeq"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FChargeLeader"] != null && ds.Tables[0].Rows[0]["FChargeLeader"].ToString() != "")
                {
                    model.FChargeLeader = ds.Tables[0].Rows[0]["FChargeLeader"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FParentDeptNo"] != null && ds.Tables[0].Rows[0]["FParentDeptNo"].ToString() != "")
                {
                    model.FParentDeptNo = ds.Tables[0].Rows[0]["FParentDeptNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FIsEval"] != null && ds.Tables[0].Rows[0]["FIsEval"].ToString() != "")
                {
                    model.FIsEval = ds.Tables[0].Rows[0]["FIsEval"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FDeptShorName"] != null)
                {
                    model.FDeptShorName = ds.Tables[0].Rows[0]["FDeptShorName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FIsReportPlanSummary"] != null)
                {
                    model.FIsReportPlanSummary = ds.Tables[0].Rows[0]["FIsReportPlanSummary"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FIsCheckSystem"] != null)
                {
                    model.FIsCheckSystem = ds.Tables[0].Rows[0]["FIsCheckSystem"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 是否为外派机构
        /// </summary>
        ///<creator>chenly</creator>
        /// <date>2013-09-27</date>
        /// <param name="deptNo">部门编号</param>
        /// <returns>是否为外派机构</returns>
        public string IsAgency(string deptNo, string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FIsAgency from t_HRDept");
            strSql.Append(" where FDeptNo=@FDeptNo and FEntityNo=@FEntityNo");

            SqlParameter[] parameters = {
					new SqlParameter("@FDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)};
            parameters[0].Value = deptNo;
            parameters[1].Value = entityNo;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (null != obj)
            {
                return obj.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取所有的部门 FDeptNo,FDeptName,FParentDeptNo
        /// <creator>sunlj</creator>
        /// <date>2013-10-16</date>
        /// </summary>
        /// <param name="strEntityNo"></param>
        /// <returns></returns>
        public DataSet GetDeptListAll(string strEntityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FDeptNo,FDeptName,FParentDeptNo,FUnionScoreType ");
            strSql.Append(" FROM t_HRDept  where FEntityNo=@FEntityNo order by FSeq");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = strEntityNo;

            return DbHelperSQL.Query(strSql.ToString(), parameters);

        }

        /// <summary>
        /// 获取部门 FDeptNo,FDeptName,FParentDeptNo（虚拟部门除外）
        /// <creator>sunlj</creator>
        /// <date>2013-10-17</date>
        /// </summary>
        /// <param name="strEntityNo">单位编号</param>
        /// <param name="decYear">年度</param>
        /// <returns></returns>
        public DataSet getDeptListNoVirtual(string strEntityNo, decimal decYear)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FDeptNo,FDeptName,FParentDeptNo,FUnionScoreType,FIsAgency,FSeq ");
            strSql.Append(" FROM t_HRDept  where FEntityNo=@FEntityNo and FDeptProp <> '9' ");
            strSql.Append("and (FDeptNo in(select FDeptNo from t_chkInfoStdLocal where FYear=@FYear group by FDeptNo having sum(FBaseScore)>0)");
            strSql.Append("or FDeptNo in(select FDeptNo from t_chkInfoStdSuper where FYear=@FYear group by FDeptNo having sum(FBaseScore)>0))");
            strSql.Append(" order by FSeq");
            SqlParameter[] parameters = {
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50),
                    new SqlParameter("@FYear", SqlDbType.NVarChar,50)};
            parameters[0].Value = strEntityNo;
            parameters[1].Value = decYear;
            return DbHelperSQL.Query(strSql.ToString(), parameters);

        }
        /// <summary>
        /// 部门排序
        /// <creator>wangl</creator>
        /// <date>2013-08-02</date> 
        /// </summary>
        /// <param name="deptNos">部门编号集合</param>
        /// <param name="entityNo">公司编号</param>
        public void SeqDeptTop(string[] deptNos, string entityNo)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds;
            StringBuilder strSql;
            int intTopSeq = 0;
            foreach (string deptNo in deptNos)
            {
                intTopSeq++;
                cmds = new CommandInfo();
                strSql = new StringBuilder();
                strSql.Append("update t_HRDept set FSeq=@FSeq where FDeptNo=@FDeptNo and FEntityNo=@FEntityNo");

                SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FDeptNo", SqlDbType.NVarChar,12),					
					new SqlParameter("@FSeq", SqlDbType.Int,4)};

                parameters[0].Value = entityNo;
                parameters[1].Value = deptNo;
                parameters[2].Value = intTopSeq;

                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters;
                cmdList.Add(cmds);
            }

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);
        }

        /// <summary>
        /// 获取部门 FDeptNo,FDeptName,FParentDeptNo（虚拟部门除外）
        /// <creator>sunlj</creator>
        /// <date>2013-10-17</date>
        /// </summary>
        /// <param name="strEntityNo">单位编号</param>
        /// <param name="decYear">年度</param>
        /// <returns></returns>
        public DataSet getDeptListNoVirtual(string strEntityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FDeptNo,FDeptName,FParentDeptNo,FUnionScoreType,FIsAgency,FSeq ");
            strSql.Append(" FROM t_HRDept  where FEntityNo=@FEntityNo and FDeptProp <> '9' ");
            strSql.Append(" order by FSeq");
            SqlParameter[] parameters = {
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50),
                    new SqlParameter("@FYear", SqlDbType.NVarChar,50)};
            parameters[0].Value = strEntityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);

        }

        public string GetFShowContent(string entityNO, string FDeptNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FShowContent from dbo.t_DeptShowContent");
            strSql.Append(" where FEntityNo=@FEntityNo and FDeptNo=@FDeptNo");
            SqlParameter[] parameters ={
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12),
                    new SqlParameter("@FDeptNo",SqlDbType.NVarChar,12)};
            parameters[0].Value = entityNO;
            parameters[1].Value = FDeptNo;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (null != obj)
            {
                return obj.ToString();
            }
            else
            {
                return string.Empty;
            }
        }
        public void SetFShowContent(string FShowContent,string entityNO, string FDeptNo)
        {

            StringBuilder strSql = new StringBuilder();
         //   strSql.Append("UPDATE dbo.t_DeptShowContent SET FShowContent=@FShowContent WHERE FDeptNo=@FDeptNo AND FEntityNo =@FEntityNo");
            strSql.Append(@"IF EXISTS(SELECT * FROM dbo.t_DeptShowContent WHERE FDeptNo=@FDeptNo AND FEntityNo =@FEntityNo)


UPDATE dbo.t_DeptShowContent SET FShowContent=@FShowContent WHERE FDeptNo=@FDeptNo AND FEntityNo =@FEntityNo
ELSE
INSERT dbo.t_DeptShowContent
        ( FEntityNo, FDeptNo, FShowContent )
VALUES  ( @FEntityNo,           @FDeptNo ,           @FShowContent           )");
            SqlParameter[] parameters ={
                                                new SqlParameter("@FShowContent",SqlDbType.NVarChar,5000),
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12),
                    new SqlParameter("@FDeptNo",SqlDbType.NVarChar,12)
                                           };
           parameters[0].Value = FShowContent;
            parameters[1].Value = entityNO;
            parameters[2].Value = FDeptNo;
            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

    }
}
